import xlrd, xlwt
from common.big_screen_interface import BigScreenInterface
import json
import time

class WriteExcel():
    def __init__(self,sheet_name):
        # 创建一个workbook 设置编码  和worksheet
        self.wb = xlwt.Workbook(encoding='utf-8')
        # self.ws = self.wb.active_sheet
        self.ws = self.wb.add_sheet(sheet_name)

    def write_excel_head(self,head_col0,head_col1,head_col2,head_col3,head_col4,head_col5,head_col6,):
        self.ws.write(0, 0,head_col0)
        self.ws.write(0, 1,head_col1)
        self.ws.write(0, 2,head_col2)
        self.ws.write(0, 3, head_col3)
        self.ws.write(0, 4, head_col4)
        self.ws.write(0, 5, head_col5)
        self.ws.write(0, 6, head_col6)

    def write_excel_body(self, row, col, value):
        # 数据写入excel,参数对应 行, 列, 值
        self.ws.write(row, col, value)  # 不带样式的写入

    def save_excel(self,excel_name):
        current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        self.wb.save('/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_{}_{}.xls'.format(excel_name,current_time))


class ReadExcel(object):
    def __init__(self, data_path, sheetname, wenxin_step1_url,wenxin_step1_static,wenxin_step1_ttsAndChart):
        #定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows
        self.wenxin_step1_url = wenxin_step1_url
        self.wenxin_step1_static = wenxin_step1_static
        self.wenxin_step1_ttsAndChart = wenxin_step1_ttsAndChart

    def readExcel(self):
        data = []
        for i in range(1, self.rowNum):
            # 获取单元格数据
            except_type = self.table.cell_value(i, 1)
            content = self.table.cell_value(i, 2)
            contents = content.split('\n')

            # print(contents)
            for content in contents:
                # 调用文心接口,拿到返回的type和对应的method
                obj = BigScreenInterface(self.wenxin_step1_url)
                res, r_elapsed = obj.wenxin_step1(content)
                res_dic = json.loads(res)
                # 将content，返回type写入excel
                if self.sheetname == "数据分析类指令":
                    # 数据分析指令，需要调用另一个接口
                    obj_statistic = BigScreenInterface(self.wenxin_step1_static)
                    res_statistic, r_elapsed_statistics = obj_statistic.wenxin_step1(content)
                    res_statistic_dic = json.loads(res_statistic)
                    data.append({"content": content, "res": res_dic, "res_statistic_dic": res_statistic_dic,
                                 "except_type": except_type, "r_elapsed_statistics": r_elapsed_statistics,"r_elapsed": r_elapsed})
        return data

    def readQuesAndData(self):
        data = []
        for i in range(1, self.rowNum):
            # 获取请求体
            QuesAndData = self.table.cell_value(i, 3)

            # 调用文心接口,拿到返回的type和对应的method
            obj = BigScreenInterface(self.wenxin_step1_ttsAndChart)
            res, r_elapsed = obj.wenxin_step1_ttsAndChart(QuesAndData)
            try:
                res_dic = json.loads(res)
            except Exception as e:
                res_dic = str(e)
            try:
                ttsData = res_dic["data"]["tts"]
            except Exception as e:
                ttsData = str(e)
            try:
                chartType = res_dic["data"]["type"]
            except Exception as e:
                chartType = str(e)
            # 将content，返回type写入excel
            data.append({"tts": ttsData, "chart": chartType, "res_dic": res_dic})
        return data

def acquire_ttsAndchart(data):
    writeObj = WriteExcel("数据分析类指令")
    writeObj.write_excel_body(0, 0, 'chart')
    writeObj.write_excel_body(0, 1, 'tts')
    writeObj.write_excel_body(0, 2, 'resData')

    for count in range(len(data)):
        writeObj.write_excel_body(count+1, 0, str(data[count]["chart"]))
        writeObj.write_excel_body(count+1, 1, str(data[count]["tts"]))
        writeObj.write_excel_body(count+1, 2, str(data[count]["res_dic"]))
    writeObj.save_excel("chart类型和tts")

def generate_excel_body(data):
    current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
    save_path = '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_数据分析_{}.json'.format(current_time)
    with open(save_path,'w',encoding='utf-8') as f:
        for i in range(len(data)):
            # 1：第一列
            cmd = data[i]["content"]
            # 2：第二列
            try:
                cmdType = data[i]["res"]["data"]["type"]
            except Exception as e:
                cmdType = "无data.type字段"
            # 3:第三列
            expType = data[i]["except_type"]
            # 第四列 意图识别结论
            if cmdType == expType:
                result = "PASS"
            else:
                result = "FAIL"

            reqChatTime = ''
            respCtrlScreenTime = ''
            reqContentTime = ''
            reqGenSql = ''
            respSql = ''
            execSqlTime = ''
            sqlToJsonTime = ''
            reqWenxinChat = ''
            respWenxinChat = ''
            endTime = ''
            totalCost = ''
            intentCost = ''
            genSqlCost = ''
            execSqlCost = ''
            genChatCost = ''
            statisticData = ''
            respData = json.dumps((data[i]["res"]),ensure_ascii=False)

            if result == "PASS":
                # 第5列   请求聊天内容时间
                try:
                    for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "请求意图参数":
                            reqChatTime = int(m["timestamp"])
                except Exception as e:
                    reqChatTime = "未获取到请求意图参数时间"
                # 第6列   控屏返回时间
                try:
                    for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                        if m["type"]== "意图识别返回":
                            respCtrlScreenTime = int(m["timestamp"])
                except Exception as e:
                    respCtrlScreenTime = "未获取到意图识别返回时间"
                # 第7列   请求数据分析内容
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "请求数据分析参数":
                            reqContentTime = int(m["timestamp"])
                except Exception as e:
                    reqContentTime = "未获取到请求数据分析参数时间"
                # 第8列   请求数据分析内容JSON时间
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "请求生成SQL":
                            reqGenSql = int(m["timestamp"])
                except Exception as e:
                    reqGenSql = "未获取到请求生成SQL时间"
                # 第9列 数据分析返回时间
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "生成SQL返回":
                            respSql = int(m["timestamp"])
                except Exception as e:
                    respSql = "未获取到生成SQL返回时间"
                # 第10列 执行的SQL语句
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "执行的SQL语句":
                            execSqlTime = int(m["timestamp"])
                except Exception as e:
                    execSqlTime = "未获取到执行的SQL语句时间"
                # 第11列 SQL结果转换JSON
                # print(data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"])
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "SQL结果转换JSON":
                            sqlToJsonTime = int(m["timestamp"])
                except Exception as e:
                    sqlToJsonTime = "未获取到SQL结果转换JSON时间"
                # 第12列 请求文心分析图表
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "请求图表分析":
                            reqWenxinChat = int(m["timestamp"])
                except Exception as e:
                    reqWenxinChat = "未获取到请求文心分析图表时间"
                # 第13列 使用图表分析返回
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "图表分析返回":
                            respWenxinChat = int(m["timestamp"])
                except Exception as e:
                    respWenxinChat = "未获取到使用图表分析返回时间"
                # 第14列 结束请求，开始响应内容
                try:
                    for m in data[i]["res_statistic_dic"]["data"]["trace"]["chatRes"]:
                        if m["type"] == "开始响应":
                            endTime = int(m["timestamp"])
                except Exception as e:
                    endTime = "未获取到结束请求，开始响应内容时间"
                # 第15列 总耗时
                try:
                    totalCost = int(data[i]["res_statistic_dic"]["data"]["trace"]["spendTime"])
                except Exception as e:
                    totalCost = "未获取到总耗时"
                # 第16列 意图识别耗时
                try:
                    intentCost = respCtrlScreenTime - reqChatTime
                except Exception as e:
                    intentCost = "获取时间戳失败"
                # 第17列 生成sql耗时
                try:
                    genSqlCost = respSql - reqGenSql
                except Exception as e:
                    genSqlCost = "获取时间戳失败"
                # 第18列 执行sql耗时
                try:
                    execSqlCost = reqWenxinChat - execSqlTime
                except Exception as e:
                    execSqlCost = "获取时间戳失败"
                # 第19列 生成图表播报耗时
                try:
                    genChatCost = respWenxinChat - reqWenxinChat
                except Exception as e:
                    genChatCost = "获取时间戳失败"

                # 第20列 返回的statistic内容
                try:
                    # statisticData = str((data[i]["res_statistic_dic"]["data"]["statistic"]))
                    statisticData = json.dumps((data[i]["res_statistic_dic"]["data"]["statistic"]),ensure_ascii=False)
                except Exception as e:
                    statisticData = "未获取到返回的statistic内容"
                # 第21列 备注
                try:
                    # respData = str((data[i]["res_statistic_dic"]["data"]))
                    respData = json.dumps((data[i]["res_statistic_dic"]["data"]),ensure_ascii=False)
                except Exception as e:
                    respData = json.dumps((data[i]["res_statistic_dic"]),ensure_ascii=False)

            json_data = {'cmd':cmd,'cmdType':cmdType,'expType':expType,'result':result,'reqChatTime':reqChatTime,
                         'respCtrlScreenTime':respCtrlScreenTime,'reqContentTime':reqContentTime,'reqGenSql':reqGenSql,'respSql':respSql,
                         'execSqlTime':execSqlTime,'sqlToJsonTime':sqlToJsonTime,'reqWenxinChat':reqWenxinChat,'respWenxinChat':respWenxinChat,
                         'endTime':endTime,'totalCost':totalCost,'intentCost':intentCost,'genSqlCost':genSqlCost,
                         'execSqlCost':execSqlCost,'genChatCost':genChatCost,'statisticData':statisticData,'respData':respData,}
            json.dump(json_data, f,ensure_ascii = False)
            json_data.clear()




def main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static,testType,wenxin_step1_ttsAndChart):
    read_obj = ReadExcel(data_path, sheetname, wenxin_step1_url, wenxin_step1_static,wenxin_step1_ttsAndChart)

    if testType == 'ttsAndChart':
        data = read_obj.readQuesAndData()
        acquire_ttsAndchart(data)
    elif testType == 'all':
        data = read_obj.readExcel()
        generate_excel_body(data)
    else:
        print('请输入正确的testType类型')


if __name__ == "__main__":

    data_path = "/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/resource/大屏数字人指令集-test.xlsx"

    # testType = 'ttsAndChart'
    testType = 'all'

    sheetname_list = ["数据分析类指令"]
    wenxin_step1_url = "https://219.135.214.196:9001/v2x/platform/wenxin/v2/chat"
    wenxin_step1_static = "https://219.135.214.196:9001/v2x/platform/wenxin/v2/chat/statistic"
    wenxin_step1_ttsAndChart = "http://172.20.137.70:8866/api/chart/query"

    for sheetname in sheetname_list:

        main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static,testType,wenxin_step1_ttsAndChart)








